<%@ page language="java" import="org.json.*,java.sql.*,java.util.*,com.microsoft.sqlserver.jdbc.*,java.io.*,web.rec.*" 
contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"  %>

<% 
	Connection conn = ResourceTool.getConnection();
%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
<script type="text/javascript" src="script/jquery-1.6.3.min.js"></script>
<script type="text/javascript" src="script/form_script.js"></script>
</head>
<body>

            <%
			try {
			// Create the prepared statement and use it to
			// INSERT the student attrs INTO the Student table.
			Statement stmt = conn.createStatement();
			ResultSet rs;
			%>
			<table>
				<tr>
					<th>Course Num</th>
					<th>Professor</th>
					<th>year</th>
					<th>quarter</th>
				</tr>
				<tr>
					<form action="DecisionSupport.jsp" method="get">
						<input type="hidden" value="submit" name="action">
						<td><input value="" name="course_num"></td>
						<td><input value="" name="faculty_name"></td>
						<td><input value="" name="year"></td>
						<td>
						<select name="season">
						<option value="Winter">Winter</option>
						<option value="Spring">Spring</option>
						<option value="Summer">Summer</option>
						<option value="Fall">Fall</option>
						</select>
						</td>
						<td><input type="submit" value="submit"></td>
					</form>
				</tr>

			<%
			String action = request.getParameter("action");
			if (action != null && action.equals("submit")) {
				String course_num = request.getParameter("course_num");
				String faculty_name = request.getParameter("faculty_name");
				String year = request.getParameter("year");
				String season = request.getParameter("season");
				int [] gradeArray = new int[5];
				int units = 0,total = 0,index;
				float grade = 0 ,totalGrade = 0;
				String letterGrade = null;
				float gpa = 0;
				%>
				<tr>
				<td>statistics for <%= course_num %>, <%= faculty_name %>, <%= year %>, <%= season %> :</td>
				</tr>
				<%
				String sql = "select * from course, class, section, StudentTakingSection where "
						+ "course.course_num =  class.course_num and "
						+ "class.class_id =  section.class_id and "
						+ "StudentTakingSection.section_id = section.section_id and "
						+ "course.course_num = '" + course_num + "' and "
						+ "class.year = " + year + " and class.season = '"+ season + "' and "
						+ "section.instructor = '" + faculty_name + "'";
				rs =  stmt.executeQuery(sql);
			    for (int i = 0; i< 5;i++)
			    {
			    	gradeArray[i] = 0;
			    }
				while(rs.next())
				{
					letterGrade = rs.getString("grade");
					index = ResourceTool.gradeToIndex(letterGrade);
					gradeArray[index]++; 
				}
				%>
				<tr>
				<td>A: </td>
				<td><%= gradeArray[0] %></td>
				</tr>
				<tr>
				<td>B: </td>
				<td><%= gradeArray[1] %></td>
				</tr>
				<tr>
				<td>C: </td>
				<td><%= gradeArray[2] %></td>
				</tr>
				<tr>
				<td>D: </td>
				<td><%= gradeArray[3] %></td>
				</tr>
				<tr>
				<td>others:</td>
				<td><%= gradeArray[4] %></td>
				</tr>
				<tr>
				<td>statistics for <%= course_num %>, <%= faculty_name %> over the years:</td>
				</tr>
				<%
				sql = "select * from course, class, section, StudentTakingSection where "
						+ "course.course_num =  class.course_num and "
						+ "class.class_id =  section.class_id and "
						+ "StudentTakingSection.section_id = section.section_id and "
						+ "course.course_num = '" + course_num + "' and "
						+ "section.instructor = '" + faculty_name + "'";
				System.out.println(sql);
				rs =  stmt.executeQuery(sql);
			    for (int i = 0; i< 5;i++)
			    {
			    	gradeArray[i] = 0;
			    }
				while(rs.next())
				{
					letterGrade = rs.getString("grade");
					index = ResourceTool.gradeToIndex(letterGrade);
					gradeArray[index]++; 
				}

				%>
				<tr>
				<td>A: </td>
				<td><%= gradeArray[0] %></td>
				</tr>
				<tr>
				<td>B: </td>
				<td><%= gradeArray[1] %></td>
				</tr>
				<tr>
				<td>C: </td>
				<td><%= gradeArray[2] %></td>
				</tr>
				<tr>
				<td>D: </td>
				<td><%= gradeArray[3] %></td>
				</tr>
				<tr>
				<td>others:</td>
				<td><%= gradeArray[4] %></td>
				</tr>
				<tr>
				<td>statistics for <%= course_num %> over the years :</td>
				</tr>
				<%
				sql = "select * from course, class, section, StudentTakingSection where "
						+ "course.course_num =  class.course_num and "
						+ "class.class_id =  section.class_id and "
						+ "StudentTakingSection.section_id = section.section_id and "
						+ "course.course_num = '" + course_num + "' and "
						+ "section.instructor = '" + faculty_name + "'";
				rs =  stmt.executeQuery(sql);
			    for (int i = 0; i< 5;i++)
			    {
			    	gradeArray[i] = 0;
			    }
				while(rs.next())
				{
					letterGrade = rs.getString("grade");
					index = ResourceTool.gradeToIndex(letterGrade);
					gradeArray[index]++; 
				}

				%>
				<tr>
				<td>A: </td>
				<td><%= gradeArray[0] %></td>
				</tr>
				<tr>
				<td>B: </td>
				<td><%= gradeArray[1] %></td>
				</tr>
				<tr>
				<td>C: </td>
				<td><%= gradeArray[2] %></td>
				</tr>
				<tr>
				<td>D: </td>
				<td><%= gradeArray[3] %></td>
				</tr>
				<tr>
				<td>others:</td>
				<td><%= gradeArray[4] %></td>
				</tr>
				<tr>
				<td>average GPA for <%= course_num %>, <%= faculty_name %> over the years :</td>
				</tr>
				<%
				sql = "select units, grade from course, class, section, StudentTakingSection where "
						+ "course.course_num =  class.course_num and "
						+ "class.class_id =  section.class_id and "
						+ "StudentTakingSection.section_id = section.section_id and "
						+ "course.course_num = '" + course_num + "' and "
						+ "section.instructor = '" + faculty_name + "'";
				rs =  stmt.executeQuery(sql);
				while(rs.next())
				{
					letterGrade = rs.getString(2);
					if(letterGrade.toLowerCase().equals("s")
					 ||letterGrade.toLowerCase().equals("u")
					 ||letterGrade.toLowerCase().equals("in"))
						continue;
					units = rs.getInt(1);
					grade = Float.parseFloat(letterGrade);
					total += units;
					totalGrade += units * grade;
					if(total != 0)
					{
						gpa = totalGrade/total;
					}
				}
				%>
				<tr>
				<td><%= gpa %></td>
				</tr>
				<% 
			}
			} catch (SQLException sqle) {
				out.println(sqle.getMessage());
			} catch (Exception e) {
				out.println(e.getMessage());
			}    
            %>
		</table>
</body>
</html>